package com.F03.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.F03.actionform.F0306ActionForm;
import com.F03.info.PdPlanningInfo;
import com.dao.DataOperations;
import com.dbtools.Dispose;
import com.exception.SelectException;
import com.util.CommonTools;

public class F0306DAO extends DataOperations{

	Logger log = Logger.getLogger(F0306DAO.class);
	public F0306DAO(){
		
	}
    public F0306DAO(Connection connection){
        super(connection);
    }
   
    public List queryProductConsumptionList(F0306ActionForm form) throws SelectException{
    	List list = new ArrayList();
    	StringBuffer sql = new StringBuffer();
    	
    	sql.append(" select g.manufacturing_order,k.name,g.material_cost,h.unit_price, ");
    	sql.append(" h.total_price,f.consumption, ");
    	sql.append(" (CONVERT(float,g.material_cost)/CONVERT(float,h.unit_price))*100 as consumption_rate");
    	sql.append(" from t_not_standard_products g,c_orders h,t_product_model k,");
    	sql.append(" (select sum(e.consumption) as consumption,e.manufacturing_order from "); 
    	sql.append(" (select max(c.serial) as max_serial,a.manufacturing_order,a.groups ");
    	sql.append(" ,a.group_qty*adyh.FUN_GETWIPPRODUCTCOST(a.manufacturing_order,max(c.serial)) as consumption ");
    	sql.append(" from pd_production a,t_not_standard_products b,t_family c ");
    	sql.append(" where a.manufacturing_order = b.manufacturing_order and ");
    	sql.append(" b.family_code = c.family_code and a.last_advance = c.phases_code  "); 
    	if(!form.getLastAdvanceDate1().equals("")){
    		sql.append(" and a.last_advance_date >=CONVERT(datetime,'"+form.getLastAdvanceDate1()+"') ");
    	}
    	if(!form.getLastAdvanceDate2().equals("")){
    		sql.append(" and a.last_advance_date <=CONVERT(datetime,'"+form.getLastAdvanceDate2()+"') ");
    	}
    	sql.append(" group by a.manufacturing_order ,a.groups,a.group_qty ");
    	sql.append(" ) e group by e.manufacturing_order ) f ");
    	sql.append(" where g.manufacturing_order= f.manufacturing_order and g.model_type=k.code and ");
    	sql.append(" g.manufacturing_order = h.manufacturing_order ");
    	if(!form.getManufacturingOrder1().equals("")){
    		sql.append(" and g.manufacturing_order>='"+form.getManufacturingOrder1()+"' ");
    	}
    	if(!form.getManufacturingOrder2().equals("")){
    		sql.append(" and g.manufacturing_order<='"+form.getManufacturingOrder2()+"' ");
    	}
    	
    	float materialCost =0;
    	float unitPrice =0;
    	float totalPrice =0;
    	float consumption =0;
    	float materialCostTotal =0;
    	float unitPriceTotal =0;
    	float totalPriceTotal =0;
    	float consumptionTotal =0;
    	double consumptionRateTotal =0;
    	ResultSet rest = null;
    	try{
    		log.info(sql.toString());
    		rest = this.executeQuery(sql.toString());
    		while(rest.next()){
    			Map map = new HashMap();
    			materialCost = Float.parseFloat(rest.getString("material_cost"));
    			unitPrice = Float.parseFloat(rest.getString("unit_price"));
    			totalPrice = Float.parseFloat(rest.getString("total_price"));
    			consumption = Float.parseFloat(rest.getString("consumption"));
    			
				map.put("manufacturingOrder", CommonTools.null2String(rest.getString("manufacturing_order")));
				map.put("model", CommonTools.null2String(rest.getString("name")));
				map.put("materialCost", CommonTools.convertCurrencyRMB(rest.getString("material_cost")));
				map.put("unitPrice", CommonTools.convertCurrencyRMB(rest.getString("unit_price")));
				map.put("totalPrice", CommonTools.convertCurrencyRMB(rest.getString("total_price")));
				map.put("consumption", CommonTools.convertCurrencyRMB(rest.getString("consumption")));
				map.put("consumptionRate", CommonTools.getTwoDecimal(rest.getString("consumption_rate")));
				list.add(map);
				
				materialCostTotal = materialCostTotal+materialCost;
		    	unitPriceTotal = unitPriceTotal+unitPrice;
		    	totalPriceTotal = totalPriceTotal+ totalPrice;
		    	consumptionTotal = consumptionTotal+ consumption;
    		}
    		
    		if(consumptionTotal!=0){
    			consumptionRateTotal = (consumptionTotal/totalPriceTotal)*100;
    			form.setMaterialCostTotal(CommonTools.convertCurrencyRMB(String.valueOf(materialCostTotal)));
    			form.setUnitPriceTotal(CommonTools.convertCurrencyRMB(String.valueOf(unitPriceTotal)));
    			form.setTotalPriceTotal(CommonTools.convertCurrencyRMB(String.valueOf(totalPriceTotal)));
    			form.setConsumptionTotal(CommonTools.convertCurrencyRMB(String.valueOf(consumptionTotal)));
    			form.setConsumptionRateTotal(CommonTools.getTwoDecimal(String.valueOf(consumptionRateTotal)));
    			
    			form.setMaterialCostTotalXls(CommonTools.null2String(String.valueOf(materialCostTotal)));
    			form.setUnitPriceTotalXls(CommonTools.null2String(String.valueOf(unitPriceTotal)));
    			form.setTotalPriceTotalXls(CommonTools.null2String(String.valueOf(totalPriceTotal)));
    			form.setConsumptionTotalXls(CommonTools.null2String(String.valueOf(consumptionTotal)));
    			
    		}
    	}catch(SQLException e){
    		e.printStackTrace();
    	}finally{
			Dispose.dispose(rest);
		}
    	return list;
    }
    
}
